/**
 * @Description: 表的检索，组装数据
 * @author 若琳
 * @date 2021/7/1
*/

const Pager = require("../../../../../../ruoling-model/src/com/ruoling/model/SystemModel/Pager.js");

const {MYSQL} = require("../../../../../../ruoling-common/src/com/ruoling/common/config/mysqlconfig.js");



/**
 * 分页DB封装
 * 使用方式：
 *    const pager_db = await new Pager_DB(10,1).table("user").and({"key":"name","value":"小小"}).and({"key":"state","value":"1"}).select();
 */
class Pager_DB{

    /**
     * 功能
     * 1、需要写入SQL语句
     * 2、返回对应数据
     */

    constructor(limit,page_num) {
        "use strict";
        this.limit = limit || 10;
        this.page_num = page_num || 0;
        this.sql = null;
        this.config = null;
        this.countSql = null;
        this._options = {
            table:null,
            table_alias:null,
            and:[], //数组
            order:[],
            table_join:[]
        };
        this.init();
    };
    init(){
        "use strict";
        let _this = this;
        _this._options.and.push();
        _this.config = {
            "AND":" AND ",
            "WHERE":" WHERE ",
            "LIMIT":" LIMIT ",
            "SELECT":"SELECT ",
            "FROM":" FROM ",
            "AS":" AS ",
            "EQ":"=", //等于
            "LT":"<", //小于
            "GT":">",  //大于
            "LE":"<=",  //小于或等于
            "GE":">=", //大于或等于
            "COUNT_ID":" COUNT(ID) ",
            "ORDER_BY":"ORDER BY",
            "ASC":"ASC",
            "DESC":"DESC",
            "Left":"LEFT JOIN",
            "Right":"RIGHT JOIN"
        }
    }
    /**
     *
     */
    async select(){
        "use strict";
        let _this = this;
        let connect = null;

            try{
                //处理 table SQL数据
                let sql = ""+_this.config.SELECT+"*"+_this.config.FROM+"";

                //处理 table总数 SQL
                let sql_count = ""+_this.config.SELECT+"COUNT("+_this._options.table_alias+".ID)"+_this.config.AS+"num"+_this.config.FROM+""+_this._options.table+" "


                sql += ""+_this._options.table+" as "+_this._options.table_alias+"";

                let isdelete_obj = {"key":""+_this._options.table_alias+".Is_delete","value":1,"on":"="}
                _this._options.and.push(isdelete_obj)

                let table_join_arr = _this._options.table_join;
                for(let i = 0;i<table_join_arr.length;i++){
                    let t_join = "";
                    if(table_join_arr[i].type == "left"){
                        t_join = _this.config.Left;
                    }else if(table_join_arr[i].type == "right"){
                        t_join = _this.config.Right;
                    }

                    sql += " "+t_join+" "+table_join_arr[i].table+" as "+table_join_arr[i].alias+" ON "+table_join_arr[i].value+" ";
                    sql_count += " "+t_join+" "+table_join_arr[i].table+" as "+table_join_arr[i].alias+" ON "+table_join_arr[i].value+" ";
                }

                sql += ""+_this.config.WHERE+"1=1"
                sql_count += ""+_this.config.WHERE+"1=1"

                let Arr_and = _this._options.and;
                let Arr_order = _this._options.order;

                let pager =  new Pager(parseInt(_this.limit),parseInt(_this.page_num));

                for(let i  = 0;i<Arr_and.length;i++){
                    let on = "";
                    if(Arr_and[i].on == _this.config.EQ){
                        on = _this.config.EQ
                    }else if(Arr_and[i].on == _this.config.LT){
                        on = _this.config.LT
                    }else if(Arr_and[i].on == _this.config.GT){
                        on = _this.config.GT
                    }else if(Arr_and[i].on == _this.config.LE){
                        on = _this.config.LE
                    }else if(Arr_and[i].on == _this.config.GE){
                        on = _this.config.GE
                    }
                    sql += ""+_this.config.AND+""+Arr_and[i].key+""+Arr_and[i].on+"\""+Arr_and[i].value+"\"";
                    sql_count += ""+_this.config.AND+""+Arr_and[i].key+""+Arr_and[i].on+"\""+Arr_and[i].value+"\"";
                }

                //处理排序
                if(Arr_order.length != 0){
                    sql += " "+_this.config.ORDER_BY+" "
                    for(let n = 0;n<Arr_order.length;n++){
                        if(n != 0){
                            sql += ", "+Arr_order[n].key+" "+Arr_order[n].value+" "
                        }else{
                            sql += " "+Arr_order[n].key+" "+Arr_order[n].value+" "
                        }
                    }
                }

                sql += ""+_this.config.LIMIT+""+pager.GetFromIndex()+","+pager.GetPageSize()+"";

                connect  = await MYSQL().connect();

                let tablet_count = await connect.connect_query(sql_count);
                let table_data = await connect.connect_query_close(sql);
                pager.SetDataList(table_data);
                pager.SetTotalPage(tablet_count[0].num);
                pager.SetTotalRecord(tablet_count[0].num);
                return {pager}


            }catch (e){
                if(connect != null){
                    connect.close();

                }
                console.log(e)
                return e;
            }
    };

    /**
     * 添加表名,如果不设置别名，表名则是别名
     * @param value  表名
     * @param alias  别名
     * @returns {Pager_DB}
     */
    table(value,alias){
        "use strict";
        let _this = this;
        _this._options.table_alias = alias == null ? value:alias;
        _this._options.table = value;
        return _this;
    };

    /**
     * 左关联
     * @param table
     * @param alias
     * @param value  "table.ii = table.ii" 有别名就用别名，没有别名就用表名
     * @returns {Pager_DB}
     * @constructor
     */
    LeftJoin(table,value,alias){
        "use strict";
        let _this = this;
        let as = "";
        if(table && value){

            let left_obj = {
                table:table,
                alias:alias == null ? table:alias,
                value:value,
                type:"left"
            }
            _this._options.table_join.push(left_obj);
        }
        return _this;
    };

    /**
     * 右关联
     * @param table
     * @param alias
     * @param value  "table.ii = table.ii" 有别名就用别名，没有别名就用表名
     * @returns {Pager_DB}
     * @constructor
     */
    RightJoin(table,value,alias){
        "use strict";
        let _this = this;
        let as = "";
        if(table && value){

            let right_obj = {
                table:table,
                alias:alias == null ? table:alias,
                value:value,
                type:"right"
            }
            _this._options.table_join.push(right_obj);
        }
        return _this;
    };

    /**
     *
     * @param data {key:字段名,value="参数值",on="EQ"}
     */
    and(data){
        "use strict";
        let _this = this;

        if(data.value && data.key){
           let on = data.on === undefined ? "=":data.on;
           data.on = on;
           _this._options.and.push(data);
        }

        return _this;

    };

    order(param,way){
        "use strict";
        let _this = this;
        if(param){
            let key = "";
            if(!way){
                key = _this.config.ASC;
            }else{
                key = way;
            }
            _this._options.order.push({"key":param,"value":key});
        }
        return _this;
    }

};

module.exports = Pager_DB;